﻿using Dapper;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace S2_Xxxx_XxxNetApi
{
    public class MySqlDapperHelper : IDapperHelper
    {
        public int ExecuteSql(string sql)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                return con.Execute(sql);
            }
        }

        /// <summary>
        /// 此处有问题，明天重写
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageCount"></param>
        /// <returns></returns>
        public DataTable GetByPage(string sql, int pageSize, int pageNumber, out int pageCount)
        {
            //此处有问题，明天重写
            DataTable dt = GetBySql(sql);
            pageCount = dt.Rows.Count;
            DataTable newdt = dt.Clone();
            //newdt.Clear();
            int rowbegin = (pageNumber - 1) * pageSize;
            int rowend = pageNumber * pageSize;

            if (rowbegin >= dt.Rows.Count)
                return newdt;

            if (rowend > dt.Rows.Count)
                rowend = dt.Rows.Count;
            for (int i = rowbegin; i <= rowend - 1; i++)
            {
                DataRow newdr = newdt.NewRow();
                DataRow dr = dt.Rows[i];
                foreach (DataColumn column in dt.Columns)
                {
                    newdr[column.ColumnName] = dr[column.ColumnName];
                }
                newdt.Rows.Add(newdr);
            }

            return newdt;
        }

        public DataTable GetBySql(string sql)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                DataTable table = new DataTable();
                var reader = con.ExecuteReader(sql);
                table.Load(reader);
                return table;
            }
        }
    }

    public class MySqlDapperHelper<T> : IDapperHelper<T>
    {
        #region 私有
        private string GetColumns()
        {
            Type type = typeof(T);
            PropertyInfo[] listPropertyInfo = type.GetProperties();
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append(" ");
            foreach (PropertyInfo propertyInfo in listPropertyInfo)
            {
                stringBuilder.Append(propertyInfo.Name + ",");
            }
            return stringBuilder.ToString().TrimEnd(',') + " ";
        }

        private string GetValuesColumns(T model)
        {
            Type type = model.GetType();
            PropertyInfo[] listPropertyInfo = type.GetProperties();
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append(" ");
            foreach (PropertyInfo propertyInfo in listPropertyInfo)
            {
                if (propertyInfo.Name == "ObjectID")
                {
                    if (propertyInfo.GetValue(model) == null)
                    {
                        stringBuilder.Append("uuid(),");
                    }
                    else
                    {
                        stringBuilder.Append("@" + propertyInfo.Name + ",");
                    }
                }
                else
                {
                    stringBuilder.Append("@" + propertyInfo.Name + ",");
                }
            }
            return stringBuilder.ToString().TrimEnd(',') + " ";
        }

        private string GetSetColumns()
        {
            Type type = typeof(T);
            PropertyInfo[] listPropertyInfo = type.GetProperties();
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append(" ");
            foreach (PropertyInfo propertyInfo in listPropertyInfo)
            {
                stringBuilder.Append(propertyInfo.Name + "=@" + propertyInfo.Name + ",");
            }
            return stringBuilder.ToString().TrimEnd(',') + " ";
        }

        private string GetTableName()
        {
            Type type = typeof(T);
            return type.Name;
        }

        private string GetWhereForKey(T model)
        {
            Type type = model.GetType();
            PropertyInfo[] listPropertyInfo = type.GetProperties();
            StringBuilder stringBuilder = new StringBuilder();
            if (listPropertyInfo != null && listPropertyInfo.Length != 0)
            {
                stringBuilder.Append(" where 1=1");
            }
            bool blHasKey = false;
            foreach (PropertyInfo propertyInfo in listPropertyInfo)
            {
                if (propertyInfo.GetValue(model) != null && propertyInfo.Name == "ObjectID")
                {
                    stringBuilder.Append($" and {propertyInfo.Name}=@{propertyInfo.Name} ");
                    blHasKey = true;
                }
            }

            if (blHasKey)
            {
                return stringBuilder.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        private string GetWhere(string where)
        {
            if (string.IsNullOrEmpty(where))
            {
                return string.Empty;
            }
            else
            {
                return "where " + where;
            }
        }

        private string GetWhere(object param)
        {
            if (param == null)
            {
                return string.Empty;
            }

            Type type = param.GetType(); ;
            PropertyInfo[] listPropertyInfo = type.GetProperties();
            StringBuilder stringBuilder = new StringBuilder();
            if (listPropertyInfo != null && listPropertyInfo.Length != 0)
            {
                stringBuilder.Append(" where 1=1");
            }
            foreach (PropertyInfo propertyInfo in listPropertyInfo)
            {
                if (propertyInfo.GetValue(param) != null)
                {
                    stringBuilder.Append($" and {propertyInfo.Name}=@{propertyInfo.Name} ");
                }
            }

            return stringBuilder.ToString();
        }
        #endregion

        public List<T> Get()
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = $"select {GetColumns()} from {GetTableName()};";
                return con.Query<T>(sql).ToList();
            }
        }

        public List<T> Get(string where)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = $"select {GetColumns()} from {GetTableName()} {GetWhere(where)};";
                return con.Query<T>(sql).ToList();
            }
        }

        public List<T> Get(string where, string orderBy, string orderByType)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = $"select {GetColumns()} from {GetTableName()} {GetWhere(where)} order by {orderBy} {orderByType};";
                return con.Query<T>(sql).ToList();
            }
        }

        public List<T> Get(string where, string orderBy, string orderByType, int topNumber)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {

                string sql = $"select {GetColumns()} from {GetTableName()} {GetWhere(where)} order by {orderBy} {orderByType} limit {topNumber} ;";
                return con.Query<T>(sql).ToList();
            }
        }

        public T GetFirst(string where)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = $"select {GetColumns()} from {GetTableName()} {GetWhere(where)}  limit 1 ;";
                List<T> list = con.Query<T>(sql).ToList();
                if (list == null)
                {
                    return default(T);
                }
                else
                {
                    return list[0];
                }
            }
        }

        public T GetFirst(string where, string orderBy, string orderByType)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = sql = $"select {GetColumns()} from {GetTableName()} {GetWhere(where)} order by {orderBy} {orderByType} limit 1 ;";
                List<T> list = con.Query<T>(sql).ToList();
                if (list == null)
                {
                    return default(T);
                }
                else
                {
                    return list[0];
                }
            }
        }

        public List<T> GetByPage(string where, string orderBy, string orderByType, int pageSize, int pageNumber, out int pageCount)
        {
            //查询出数量，并为页数赋值
            int dataCount = this.GetCount(where);//param
            if ((int)(dataCount % pageSize) > 0)
            {
                pageCount = (int)(dataCount / pageSize) + 1;
            }
            else
            {
                pageCount = (int)(dataCount / pageSize);
            }

            //算出跳的值
            int offSet = (pageNumber - 1) * pageSize;

            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = sql = $"select {GetColumns()} from {GetTableName()} {GetWhere(where)} order by {orderBy} {orderByType} limit {offSet},{pageSize} ;";
                return con.Query<T>(sql).ToList();
            }
        }

        public DataTable GetBySql(string sql)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                DataTable dataTable = new DataTable();
                dataTable.Load(con.ExecuteReader(sql));
                return dataTable;
            }
        }

        public int ExecuteSql(string sql)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                return con.Execute(sql);
            }
        }

        public bool Exist(string where)
        {
            List<T> listT = this.Get(where);//param
            return listT != null && listT.Count > 0;
        }

        public bool Insert(T model)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = $"insert into {GetTableName()}({GetColumns()}) values({GetValuesColumns(model)}) ";
                return con.Execute(sql, model) > 0;
            }
        }

        public bool Insert(List<T> listModel)
        {
            if (listModel == null || listModel.Count == 0)
            {
                return false;
            }

            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append($"insert into {GetTableName()}({GetColumns()}) values({GetValuesColumns(listModel[0])}) ;");
                return con.Execute(stringBuilder.ToString(), listModel) > 0;
            }
        }

        public bool Edit(T model)
        {
            string keyWhere = GetWhereForKey(model);
            if (string.IsNullOrEmpty(keyWhere))
            {
                return false;
            }
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = sql = $"update  {GetTableName()} set {GetSetColumns()} {keyWhere} ;";
                return con.Execute(sql, model) > 0;
            }
        }

        public bool Edit(List<T> listModel)
        {
            if (listModel == null || listModel.Count == 0)
            {
                return false;
            }

            foreach (T model in listModel)
            {
                string keyWhere = GetWhereForKey(model);
                if (string.IsNullOrEmpty(keyWhere))
                {
                    return false;
                }
            }

            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append($"update  {GetTableName()} set {GetSetColumns()} {GetWhereForKey(listModel[0])} ;");
                return con.Execute(stringBuilder.ToString(), listModel) > 0;
            }
        }

        public bool Delete(T model)
        {
            string keyWhere = GetWhereForKey(model);
            if (string.IsNullOrEmpty(keyWhere))
            {
                return false;
            }

            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = sql = $"delete from {GetTableName()} {keyWhere}; ";
                return con.Execute(sql, model) > 0;
            }
        }

        public bool Delete(List<T> listModel)
        {
            if (listModel == null || listModel.Count == 0)
            {
                return false;
            }

            foreach (T model in listModel)
            {
                string keyWhere = GetWhereForKey(model);
                if (string.IsNullOrEmpty(keyWhere))
                {
                    return false;
                }
            }

            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append($"delete from {GetTableName()} {GetWhereForKey(listModel[0])};");

                return con.Execute(stringBuilder.ToString(), listModel) > 0;
            }
        }

        public int GetCount()
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = $"SELECT COUNT(1) FROM {GetTableName()} ;";
                DataTable dataTable = new DataTable();
                dataTable.Load(con.ExecuteReader(sql));
                return Convert.ToInt32(dataTable.Rows[0][0]);
            }
        }

        public int GetCount(string where)
        {
            using (IDbConnection con = new MySqlConnection(AppSettings.MySqlConnectString))
            {
                string sql = $"SELECT COUNT(1) FROM {GetTableName()} {GetWhere(where)} ;";
                DataTable dataTable = new DataTable();
                dataTable.Load(con.ExecuteReader(sql));
                return Convert.ToInt32(dataTable.Rows[0][0]);
            }
        }
    }
}
